﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.Text;
using System.Globalization;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_NhapthucphamDAL : ClsConnection
    {
        public DataTable Tbl_Tonghopthu_chi(bool Trangthai, DateTime FromNgay, DateTime ToNgay)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad;
            if (Trangthai == false)
            {
                commad = new OleDbCommand(QueryString.SPROC_TBL_TONGHOPTHU_CHI.Replace("@FromNgay", "#" + FromNgay.ToShortDateString() + "#").Replace("@ToNgay", "#" + ToNgay.ToShortDateString() + "#").Replace("@Trangthai", "0"), cn);
            }
            else
            {
                commad = new OleDbCommand(QueryString.SPROC_TBL_TONGHOPTHU_CHI.Replace("@FromNgay", "#" + FromNgay.ToShortDateString() + "#").Replace("@ToNgay", "#" + ToNgay.ToShortDateString() + "#").Replace("@Trangthai", "-1"), cn);
            }

            DataTable table = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);
            ////commad.CommandType = CommandType.StoredProcedure;


            try
            {

                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }

        public DataTable GetTable(int Id)
        {
            string ProName = QueryString.PRCTBLDM_THANHPHANDINHDUONG_SELECTBYNAME;
            if (Id < 0)
            {
                ProName = "SELECT TblThucpham_thucan.Tenthucan, TblDm_Donvitinh.Tendonvitinh, "
                    + "  TblThucpham_thucan.Calo, TblThanhphanhoahoc.Tphh_Protid, TblThanhphanhoahoc.Tphh_Lipid, TblThanhphanhoahoc.Tphh_GlucId, "
                    + "  TblThanhphanhoahoc.Tphh_Nuoc, TblThanhphanhoahoc.Tphh_Cellulose, TblThanhphanhoahoc.Tphh_Tro, "
                    + "  TblThanhphanhoahoc.Tpmk_Natri, TblThanhphanhoahoc.Tpmk_Kali, TblThanhphanhoahoc.Tpmk_Calci, "
                    + "  TblThanhphanhoahoc.Tpmk_Photpho, TblThanhphanhoahoc.Tpmk_Fe, TblThanhphanhoahoc.Tpmk_Axitfomic, "
                    + "  TblThanhphanhoahoc.Tpvitamin_VitaminA, TblThanhphanhoahoc.Tpvitamin_VitaminC, "
                    + "  TblThanhphanhoahoc.Tpvitamin_PP, TblThanhphanhoahoc.Tpvitamin_Caroten, "
                    + "  TblDm_Nhomthucpham_thucan.Id AS Nhom, TblThucpham_thucan.Nguyenlieu, TblThucpham_thucan.Nguondongvat, "
                    + "  TblThucpham_thucan.Tylethai, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Nam, TblThucpham_thucan.Id "
                    + " FROM ((TblDm_Nhomthucpham_thucan INNER JOIN "
                    + "  TblThucpham_thucan ON TblDm_Nhomthucpham_thucan.Id = TblThucpham_thucan.NhomthucanId) INNER JOIN "
                    + "  TblThanhphanhoahoc ON TblThucpham_thucan.Id = TblThanhphanhoahoc.IdThucpham_thucan) INNER JOIN "
                    + "  TblDm_Donvitinh ON TblThucpham_thucan.DonvitinhId = TblDm_Donvitinh.Id "
                    + " ";

            }
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(ProName, cn);
            DataTable table = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);
            ////commad.CommandType = CommandType.StoredProcedure;


            try
            {
                commad.Parameters.Add("@Id", OleDbType.Integer).Value = Id;
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }
        //
        public DataTable TblListThucpham_thucanByNhomthucphamIdandTenthucan(int NhomthucanId, String Tenthucan)
        {
            string NamePrc = QueryString.PROC_TBLTHUCPHAMTHUCANBYNHOMTHUCANIDANDTENTHUCAN;
            if (NhomthucanId <= 0)
            {
                NamePrc = "	SELECT     TblThucpham_thucan.Id AS IdThucpham_thucan, TblThucpham_thucan.Tenthucan, TblThucpham_thucan.DonvitinhId, "
 + "                       TblDm_Donvitinh.Tendonvitinh "
 + " FROM         TblThucpham_thucan INNER JOIN "
 + "                       TblDm_Donvitinh ON TblThucpham_thucan.DonvitinhId = TblDm_Donvitinh.Id "
 + " WHERE      TblThucpham_thucan.Tenthucan like '%' + @Tenthucan+'%' ";
            }
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            if (NhomthucanId > 0)
            {
                Cmm.Parameters.Add(new OleDbParameter("@NhomthucanId", NhomthucanId));
            }
            Cmm.Parameters.Add(new OleDbParameter("@Tenthucan", Tenthucan));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }
        public DataTable TblListThucpham_thucanByNhomthucphamId(int NhomthucanId)
        {
            string NamePrc = QueryString.PROC_TBLTHUCPHAMTHUCANBYNHOMTHUCANID.Replace("@NhomthucanId", NhomthucanId.ToString());
            if (NhomthucanId == 0)
                NamePrc = "	SELECT     TblThucpham_thucan.Id AS IdThucpham_thucan, TblThucpham_thucan.Tenthucan, TblThucpham_thucan.DonvitinhId, "
 + "                       TblDm_Donvitinh.Tendonvitinh "
 + " FROM         TblThucpham_thucan INNER JOIN "
 + "                       TblDm_Donvitinh ON TblThucpham_thucan.DonvitinhId = TblDm_Donvitinh.Id ";

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();


            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }
        //[][]
        public DataTable GianhapByNgayNXandThucphamthucanId(DateTime NgayNX, int Thucan_thucphamId)
        {
            string NamePrc = QueryString.SPROC_GIANHAPBYNGAYNXANDTHUCPHAMTHUCANID;
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));
            Cmm.Parameters.Add(new OleDbParameter("@Thucpham_thucanId", Thucan_thucphamId));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }
        public DataTable SelectAlSohoadonByTo_From(DateTime ToNX, DateTime FromNX)
        {
            //string NamePrc = QueryString.sproc_SelectAlHoadonNXbyFrom_toNgay;

            string NamePrc = "SELECT NgayNX, Tongtien, Trangthai, Id "
                            + "FROM TblHoadonnhapxuat "
                            + "WHERE Trangthai = -1 AND NgayNX < #" + ToNX.AddDays(1).ToString("MM/dd/yyyy") + "# AND  TblHoadonnhapxuat.NamhocId=(select Id from TblDm_namhoc where Trangthai =-1)  and  NgayNX >= #" + FromNX.ToString("MM/dd/yyyy") + "# ";

            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            Cmm.Connection = OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }
        public DataTable SelectAlSohoadonBy(DateTime NgayNX)
        {
            QueryString.SPROC_SELECTALHOADONNXBYNGAY = "SELECT NgayNX, Tongtien, Trangthai, Id "
            + " FROM TblHoadonnhapxuat "
            + " WHERE NgayNX = #" + NgayNX.ToString("MM/dd/yyyy") + "# AND Trangthai = -1  and TblHoadonnhapxuat.NamhocId=(select Id from TblDm_namhoc where Trangthai =-1) ";
            string NamePrc = QueryString.SPROC_SELECTALHOADONNXBYNGAY;
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }
        public DataTable TblListSohoadon(DateTime NgayNX)
        {
            string NamePrc = QueryString.PROC_TBLSOHOADONBYNGAYNX;
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));

            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }
            OleDbDataAdapter Dap;
            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);

            return Dset.Tables[0];
        }
        public DataTable TblHoadonnhapxuat_GetId_ByNgay(DateTime NgayNX)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.SPROC_TBLHOADONNHAPXUAT_GET = "SELECT Id, NgayNX, Tongtien, Trangthai FROM TblHoadonnhapxuat where Trangthai = -1 and  NgayNX = #" + NgayNX + "# ORDER BY [Id] DESC ";
            OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TBLHOADONNHAPXUAT_GET, cn);
            //commad.Parameters.Add(new OleDbParameter("@NgayNX", NgayNX));
            //commad.Parameters.Add(new OleDbParameter("@Trangthai", true));
            DataTable table = new DataTable();
            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }//

        DataTable TblDanhsachhangtonByThucphamId(string Id)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();

            string SPROC_TONGHOPHANGTONKHO_FINDNAME = "SELECT     View_Tonkho.Thucpham_ThucanId, TblThucpham_thucan.Tenthucan, View_Tonkho.Soluong AS Soluongton,(select top 1 dongia from TblNhapxuathanghoa where Thucpham_ThucanId =" + Id.ToString() + " and HoadonnhapxuatId in ( select id from TblHoadonnhapxuat where trangthai=-1 ORDER BY TblHoadonnhapxuat.NgayNX desc )  ORDER BY TblNhapxuathanghoa.HoadonnhapxuatId DESC) AS Dongia , "
   + "                       TblDm_Donvitinh.Tendonvitinh "
   + " FROM         (TblDm_Donvitinh INNER JOIN "
   + "                       TblThucpham_thucan ON TblDm_Donvitinh.Id = TblThucpham_thucan.DonvitinhId) INNER JOIN "
   + "                       View_Tonkho ON TblThucpham_thucan.Id = View_Tonkho.Thucpham_ThucanId "
   + " WHERE     (View_Tonkho.Soluong <> 0 and TblThucpham_thucan.Id =" + Id.ToString() + ") ";
            //     string SPROC_TONGHOPHANGTONKHO_FINDNAME = " SELECT     View_Tonkho.Thucpham_ThucanId, TblThucpham_thucan.Tenthucan, View_Tonkho.Soluong AS Soluongton , (select top 1 dongia from TblNhapxuathanghoa where Thucpham_ThucanId =" + Id.ToString() + " and HoadonnhapxuatId in ( select id from TblHoadonnhapxuat where trangthai=-1 ORDER BY TblHoadonnhapxuat.NgayNX desc )  ORDER BY TblNhapxuathanghoa.HoadonnhapxuatId DESC) AS Dongia  ,                 " +
            //"  TblDm_Donvitinh.Tendonvitinh  FROM         (TblDm_Donvitinh INNER JOIN                        TblThucpham_thucan ON TblDm_Donvitinh.Id = TblThucpham_thucan.DonvitinhId) INNER JOIN                        View_Tonkho ON TblThucpham_thucan.Id = View_Tonkho.Thucpham_ThucanId  WHERE     (View_Tonkho.Soluong <> 0 and TblThucpham_thucan.Id =659) ";

            OleDbCommand commad = new OleDbCommand(SPROC_TONGHOPHANGTONKHO_FINDNAME, cn);

            DataTable table = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);


            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        public DataTable TblThucpham_DK(int Id, DateTime NgayNX)
        {
            OleDbConnection cn = OleDBConnect();
            string Sqlcmm = "SELECT Thucphamtheobua.Thucpham_ThucanId, Thucphamtheobua.Dongia FROM Thucphamtheobua WHERE (((Thucphamtheobua.Thucpham_ThucanId)=" + Id + ") AND ((Thucphamtheobua.Ngay)=#" + NgayNX + "#))";
            OleDbCommand commad = new OleDbCommand(Sqlcmm, cn);

            DataTable table = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);


            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                //foreach (DataRow Rw in table.Rows)
                //{
                //    Rw["Soluongton"] = Rw["Soluongton"].ToString().Trim();
                //}
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }


        public DataTable TblDanhsachhangtonByFindNameandNgayNX(string Name, DateTime NgayNX)
        {
            OleDbConnection cn = OleDBConnect();
            OleDbCommand commad = new OleDbCommand(QueryString.sproc_Tonghophangtonkho_FindNameandNgayNX.Replace("@Name", Name).Replace("@NgayNX", "#" + NgayNX.ToShortDateString() + "#"), cn);

            DataTable table = new DataTable();
            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);


            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                foreach (DataRow Rw in table.Rows)
                {
                    Rw["Soluongton"] = Rw["Soluongton"].ToString().Trim();
                }
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        public DataTable TblDanhsachhangtonByFindName(string Name)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Sql = "SELECT     View_Tonkho.Thucpham_ThucanId, TblThucpham_thucan.Tenthucan, Round(View_Tonkho.Soluong,10) AS Soluongton,                        TblDm_Donvitinh.Tendonvitinh  FROM         (TblDm_Donvitinh INNER JOIN                        TblThucpham_thucan ON TblDm_Donvitinh.Id = TblThucpham_thucan.DonvitinhId) INNER JOIN                        View_Tonkho ON TblThucpham_thucan.Id = View_Tonkho.Thucpham_ThucanId  WHERE     (View_Tonkho.Soluong > 0 and TblThucpham_thucan.Tenthucan like '%" + Name + "%')";
            //OleDbCommand commad = new OleDbCommand(QueryString.SPROC_TONGHOPHANGTONKHO_FINDNAME.Replace("@Name", Name), cn);
            OleDbCommand commad = new OleDbCommand(Sql, cn);

            DataTable table = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);


            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
                //foreach (DataRow Rw in table.Rows)
                //{
                //    try
                //    {
                //        Rw["Soluongton"] = Rw["Soluongton"].ToString();
                //    }
                //    catch { }
                //}
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            // gán lại tồn ở đây

            return table;

        }
        public DataTable TblDanhsachhangtonById_AllSchool(int Id, DateTime NgayNX)
        {

            OleDbConnection cn = ClsConnection.OleDBConnect();
            string SPROC_TONGHOPHANGTONKHO_FINDNAME = "SELECT TblThucpham_thucan.Id AS Thucpham_ThucanId, TblThucpham_thucan.DonvitinhId, TblThucpham_thucan.Tenthucan, (select Tendonvitinh from TblDm_Donvitinh where Id = TblThucpham_Thucan.DonvitinhId) AS Tendonvitinh, IIf(IsNull([TblThucpham_thucan].[Calo]),0,[TblThucpham_thucan].[Calo]) AS Kcalo, IIf([Nguondongvat],[Tphh_Protid],0) AS Protid_DV, IIf([Nguondongvat],[Tphh_Lipid],0) AS Lipid_DV, IIf([Nguondongvat],0,[Tphh_Protid]) AS Protid_TV, IIf([Nguondongvat],0,[Tphh_Lipid]) AS Lipid_TV, TblThanhphanhoahoc.Tphh_GlucId AS GlucId, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Tylethai, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Soluongton, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Soluongxuat, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Dongia, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Thanhtien, (select top 1 NgayNX from TblHoadonnhapxuat where NgayNx =#" + NgayNX.ToShortDateString() + "# ) AS NgayNX " +
           " FROM TblThanhphanhoahoc INNER JOIN TblThucpham_thucan ON TblThanhphanhoahoc.IdThucpham_thucan = TblThucpham_thucan.Id " +
 " WHERE (((TblThucpham_thucan.Id)=" + Id.ToString() + ")) ";
            OleDbCommand commad = new OleDbCommand(SPROC_TONGHOPHANGTONKHO_FINDNAME, cn);

            DataTable table = new DataTable();
            DataTable TblTon = TblDanhsachhangtonByThucphamId(Id.ToString());
            // thực phẩm lên dự kiến;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                if (table.Rows.Count > 0)
                {
                    table.Rows[0]["NgayNX"] = NgayNX;
                    table.Rows[0]["Soluongton"] = "0";
                    table.Rows[0]["Soluongxuat"] = "0";
                    table.Rows[0]["Dongia"] = "0";
                    table.Rows[0]["Thanhtien"] = "0";

                    if (TblTon.Rows.Count > 0 && TblTon.Rows[0]["Dongia"].ToString().Trim() != "")
                    {
                        table.Rows[0]["Soluongton"] = TblTon.Rows[0]["Soluongton"];
                        table.Rows[0]["Dongia"] = TblTon.Rows[0]["Dongia"];
                    }


                }
                table.Columns["Soluongxuat"].DataType = table.Columns["Kcalo"].DataType;
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        public DataTable TblDanhsachhangtonById(int Id, DateTime NgayNX)
        {

            OleDbConnection cn = ClsConnection.OleDBConnect();
            string SPROC_TONGHOPHANGTONKHO_FINDNAME = "SELECT TblThucpham_thucan.Id AS Thucpham_ThucanId, TblThucpham_thucan.DonvitinhId, TblThucpham_thucan.Tenthucan, (select Tendonvitinh from TblDm_Donvitinh where Id = TblThucpham_Thucan.DonvitinhId) AS Tendonvitinh, IIf(IsNull([TblThucpham_thucan].[Calo]),0,[TblThucpham_thucan].[Calo]) AS Kcalo, IIf([Nguondongvat],[Tphh_Protid],0) AS Protid_DV, IIf([Nguondongvat],[Tphh_Lipid],0) AS Lipid_DV, IIf([Nguondongvat],0,[Tphh_Protid]) AS Protid_TV, IIf([Nguondongvat],0,[Tphh_Lipid]) AS Lipid_TV, TblThanhphanhoahoc.Tphh_GlucId AS GlucId, TblThucpham_thucan.Tylequydoi, TblThucpham_thucan.Tylethai, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Soluongton, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Soluongxuat, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Dongia, (select Calo from TblThucpham_thucan AS T where T.Id = TblThucpham_thucan.Id) AS Thanhtien, (select top 1 NgayNX from TblHoadonnhapxuat where NgayNx =#" + NgayNX.ToShortDateString() + "# ) AS NgayNX " +
           " FROM TblThanhphanhoahoc INNER JOIN TblThucpham_thucan ON TblThanhphanhoahoc.IdThucpham_thucan = TblThucpham_thucan.Id " +
 " WHERE (((TblThucpham_thucan.Id)=" + Id.ToString() + ")) ";
            OleDbCommand commad = new OleDbCommand(SPROC_TONGHOPHANGTONKHO_FINDNAME, cn);

            DataTable table = new DataTable();
            DataTable TblTon = TblDanhsachhangtonByThucphamId(Id.ToString());
            // thực phẩm lên dự kiến;
            DataTable TblDK = this.TblThucpham_DK(Id, NgayNX);
            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                if (table.Rows.Count > 0)
                {
                    table.Rows[0]["NgayNX"] = NgayNX;
                    table.Rows[0]["Soluongton"] = "0";
                    table.Rows[0]["Soluongxuat"] = "0";
                    table.Rows[0]["Dongia"] = "0";
                    table.Rows[0]["Thanhtien"] = "0";

                    if (TblTon.Rows.Count > 0 && TblTon.Rows[0]["Dongia"].ToString().Trim() != "")
                    {
                        table.Rows[0]["Soluongton"] = TblTon.Rows[0]["Soluongton"];
                        table.Rows[0]["Dongia"] = TblTon.Rows[0]["Dongia"];
                    }
                    else
                    {
                        if (TblDK.Rows.Count > 0)
                        {
                            table.Rows[0]["Dongia"] = TblDK.Rows[0]["Dongia"];
                        }
                    }

                }
                table.Columns["Soluongxuat"].DataType = table.Columns["Kcalo"].DataType;
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        public DataTable TblDanhsachhangton()
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            string Sqltr = "SELECT     View_Tonkho.Thucpham_ThucanId, TblThucpham_thucan.Tenthucan, Round(View_Tonkho.Soluong,10) AS Soluongton,                        TblDm_Donvitinh.Tendonvitinh  FROM         (TblDm_Donvitinh INNER JOIN                        TblThucpham_thucan ON TblDm_Donvitinh.Id = TblThucpham_thucan.DonvitinhId) INNER JOIN                        View_Tonkho ON TblThucpham_thucan.Id = View_Tonkho.Thucpham_ThucanId  WHERE     (View_Tonkho.Soluong <> 0) ";
            OleDbCommand commad = new OleDbCommand(Sqltr, cn);
            DataTable table = new DataTable();

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            table.Columns.Add(Columm);


            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                //foreach (DataRow Rw in table.Rows)
                //{
                //    Rw["Soluongton"] = Rw["Soluongton"].ToString().Trim();
                //}
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        //ClsConnection ClsCnn = new ClsConnection();
        //
        public DataTable TblChitietnhapBySohoadonId(int HoadonnhapxuatId)
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            QueryString.PROC_TBLCHITIETNHAPBYSOHOADONID = "SELECT     TblNhapxuathanghoa.HoadonnhapxuatId, TblThucpham_thucan.Tenthucan, TblNhapxuathanghoa.DonvitinhId, "
            + "                       TblNhapxuathanghoa.Thucpham_ThucanId, TblDm_Donvitinh.Tendonvitinh,   "
            + "                      Round(TblNhapxuathanghoa.Soluongcon,10) AS Soluongcon,TblNhapxuathanghoa.Soluong, TblNhapxuathanghoa.Dongia "
            + " FROM         ((TblHoadonnhapxuat INNER JOIN "
            + "                       TblNhapxuathanghoa ON TblHoadonnhapxuat.Id = TblNhapxuathanghoa.HoadonnhapxuatId) INNER JOIN "
            + "                       TblThucpham_thucan ON TblNhapxuathanghoa.Thucpham_ThucanId = TblThucpham_thucan.Id) INNER JOIN "
            + "                       TblDm_Donvitinh ON TblThucpham_thucan.DonvitinhId = TblDm_Donvitinh.Id "
            + " WHERE     (TblHoadonnhapxuat.Trangthai = -1) AND (TblNhapxuathanghoa.HoadonnhapxuatId =" + HoadonnhapxuatId.ToString() + ") ";
            OleDbCommand commad = new OleDbCommand(QueryString.PROC_TBLCHITIETNHAPBYSOHOADONID, cn);
            commad.Parameters.Add(new OleDbParameter("@HoadonnhapxuatId", HoadonnhapxuatId));
            DataTable table = new DataTable();
            ////commad.CommandType = CommandType.StoredProcedure;
            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { //MessageBox.Show("Error " + ex.Message);
            }
            return table;

        }
        ClsTblDm_NamhocDAL ClsTblDm_Namhocdal = new ClsTblDm_NamhocDAL();
        public void TblNhapthucpham_Del(int HoadonnhapId)
        {
            string NamePrc1 = "delete from tblNhapxuathanghoa where(HoadonnhapxuatId =" + HoadonnhapId.ToString() + ")";
            string NamePrc2 = "delete from tblHoadonnhapxuat where(Id =" + HoadonnhapId.ToString() + ")";
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc1, Cmm);
            ClsConnection.OleDbDAdpter(NamePrc2, Cmm);
        }
        public void TblHoadonnhapxuat_Add(DateTime NgayNX, float Tongtien)
        {
            string NamePrc = "INSERT INTO TblHoadonnhapxuat (NgayNX, Tongtien, Trangthai, NamhocId ) VALUES ( #" + NgayNX.ToShortDateString() + "#, " + Tongtien.ToString() + ", ?, " + ClsTblDm_Namhocdal.ReturnNamhocId().ToString() + " )";
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Trangthai", true));
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

    }   
}
